﻿
CREATE DATABASE QUANLYNHANSU
go
USE QUANLYNHANSU
GO

--create table
CREATE TABLE NHANVIEN 
(
	MaNhanVien varchar (10) NOT NULL ,
	TenNhanVien nvarchar (50) NOT NULL,
	DiaChi nvarchar (50)  NOT NULL ,
	DienThoai nvarchar (12) ,
    NgaySinh smalldatetime NOT NULL,
	Email varchar (20) NOT NULL,
	Phai nvarchar (3) NOT NULL,
	PassWord varchar (20) NOT NULL,
	TaiKhoanNganHang varchar (20),
	MaViTri int , 
	MaTrangThai smallint not null,
	MaPhongBan int NOT NULL,
	PRIMARY KEY (MaNhanVien)	
) 
GO


CREATE TABLE THANNHAN
(
	MaThanNhan  smallint identity(1,1) ,
	TenThanNhan nvarchar (30) NOT NULL,
	NgaySinh smalldatetime NOT NULL,
	Phai nvarchar (3) NOT NULL,
	MaNhanVien varchar (10) NOT NULL ,
	PRIMARY KEY (MaThanNhan)
)
GO

CREATE TABLE NGAYPHEP 
(
	MaNgayPhep smallint identity (1,1),
	MaNhanVien varchar (10) NOT NULL ,
	PRIMARY KEY (MaNgayPhep)
) 
GO

CREATE TABLE TRANGTHAI (
	MaTrangThai smallint identity(1,1) ,
	TenTrangThai nvarchar (10) NOT NULL ,
	PRIMARY KEY (MaTrangThai)
) 
GO

CREATE TABLE KIEULAMVIEC
(
	MaKieuLamViec int identity(1,1),
	TenKieuLamViec nvarchar(100) not null,
	PRIMARY KEY (MaKieuLamViec)	
)
go
CREATE TABLE CHITIETNGAYPHEP
(
	MaChiTietNgayPhep int identity (1,1),
	MaNgayPhep smallint not null,
	NgayBatDauNghi datetime not null,
	NgayKetThucNghi datetime not null,
	PRIMARY KEY (MaChiTietNgayPhep)
)
GO
CREATE TABLE PHONGBAN
(
	MaPhongBan int identity (1,1),
	TenPhongBan nvarchar(50) not null,
	Levels int,
	PRIMARY KEY (MaPhongBan)
)
GO
CREATE TABLE PHONGBAN_VITRI
(
	MaPhongBan int not null ,
	MaViTri int not null,
	PRIMARY KEY (MaPhongBan,MaViTri)
)
GO
CREATE TABLE HOPDONG
(
	MaHopDong int identity(1,1),
	MaNhanVien varchar (10) not null, 
	NgayKyHopDong datetime not null ,
	NgayHetHan datetime not null,
	LuongCanBan money not null,
	MaKieuLamViec int not null,
	PRIMARY KEY (MaHopDong)
)
GO
CREATE TABLE VITRI
(
	MaViTri int identity (1,1),
	TenViTri nvarchar (50) not null,
	PRIMARY KEY (MaViTri)
)
go

CREATE TABLE CHITIETBANGLUONG
(
	MaChiTietBangLuong int identity(1,1),
	MaBangLuong int not null,
	LuongCanBan money not null,
	TienBaoHiem money,
	TienNghiViec money,
	TienPhuCap money,
	TienThueThuNhapCaNhan money,
	PRIMARY KEY (MaChiTietBangLuong)
)
go
CREATE TABLE LOAIBAOHIEM
(
	MaLoaiBaoHiem int identity(1,1),
	TenLoaiBaoHiem nvarchar(50) not null,
	PRIMARY KEY (MaLoaiBaoHiem)
)
go
CREATE TABLE CHITIETLOAIBAOHIEM
(
	MaChiTietLoaiBaoHiem int identity(1,1),
	MaLoaiBaoHiem int not null,
	MaNhanVien varchar(10) not null,
	TiLeDong int,
	NgayThamGia smalldatetime,
	PRIMARY KEY(MaChiTietLoaiBaoHiem)
)
go
CREATE TABLE BANGLUONG
(
	MaBangLuong int identity(1,1),
	MaNhanVien varchar(10) not null,
	Thang int not null,
	Nam int not null,
	SoTien money not null,
	PRIMARY KEY(MaBangLuong)
)
go
CREATE TABLE PHUCAP
(
	MaPhuCap int identity(1,1),
	TenPhuCap nvarchar(50) not null,
	MaNhanVien varchar(10) not null,
	SoTienPhuCap money not null,
	NgayPhuCap smalldatetime not null,
	PRIMARY KEY(MaPhuCap)
)
go
CREATE TABLE NGAYLE
(
	MaNgayLe int identity(1,1),
	NgayLe smalldatetime not null,
	PRIMARY KEY(MaNgayLe)
)
go
CREATE TABLE NGUOIDUNG
(
	MaNguoiDung int identity(1,1),
	TebDangNhap varchar(10) not null,
	MatKhau varchar(10) not null,
	Quyen varchar(10) not null,
	PRIMARY key(MaNguoiDung)
)
go


------------------------------------------------------------
ALTER TABLE HOPDONG
ADD CONSTRAINT FK_HopDong_KieuLamViec Foreign key(MaKieuLamViec) references KIEULAMVIEC(MaKieuLamViec),
	CONSTRAINT FK_HopDong_NhanVien Foreign key(MaNhanVien) references NHANVIEN(MaNhanVien)
GO

ALTER TABLE CHITIETNGAYPHEP
ADD CONSTRAINT FK_ChiTietNgayPhep_NgayPhep Foreign key(MaNgayPhep) references NGAYPHEP(MaNgayPhep)
GO

ALTER TABLE NGAYPHEP
ADD CONSTRAINT FK_NgayPhep_NhanVien Foreign key(MaNhanVien) references NHANVIEN(MaNhanVien)
GO

ALTER TABLE PHONGBAN_VITRI
ADD CONSTRAINT FK_PhongBan_ViTri Foreign key(MaPhongBan) references PHONGBAN(MaPhongBan),
	CONSTRAINT FK_PhongBan_ViTri1 Foreign key(MaViTri) references VITRI(MaViTri)
GO

ALTER TABLE NHANVIEN
ADD CONSTRAINT FK_NhanVien_TrangThai Foreign key(MaTrangThai) references TRANGTHAI(MaTrangThai),
	CONSTRAINT FK_NhanVien_ViTri Foreign key(MaViTri) references VITRI(MaViTri),
	CONSTRAINT FK_NhanVien_PhongBan Foreign key(MaPhongBan) references PHONGBAN(MaPhongBan)
GO

--table CHITIETLOAIBAOHIEM:
ALTER TABLE CHITIETLOAIBAOHIEM
	ADD CONSTRAINT FK_CHITIETLOAIBAOHIEM_LOAIBAOHIEM FOREIGN KEY(MaLoaiBaoHiem) references LOAIBAOHIEM(MaLoaiBaoHiem),
		CONSTRAINT FK_CHITIETLOAIBAOHIEM_NHANVIEN FOREIGN KEY(MaNhanVien) references NHANVIEN(MaNhanVien)
	GO

--table CHITIETBANGLUONG
ALTER TABLE CHITIETBANGLUONG
	ADD CONSTRAINT FK_CHITIETBANGLUONG_BANGLUONG FOREIGN KEY(MaBangLuong) references BANGLUONG(MaBangLuong)
	GO

--table BANGLUONG 
ALTER TABLE BANGLUONG
	ADD CONSTRAINT FK_BANGLUONG_NHANVIEN FOREIGN KEY(MaNhanVien) references NHANVIEN(MaNhanVien)
	GO

--table THANNHAN
ALTER TABLE THANNHAN
	ADD CONSTRAINT FK_THANNHAN_NHANVIEN FOREIGN KEY(MaNhanVien) references NHANVIEN(MaNhanVien)
	GO

--table PHUCAP
ALTER TABLE PHUCAP
	ADD CONSTRAINT FK_PHUCAP_NHANVIEN FOREIGN KEY(MaNhanVien) references NHANVIEN(MaNhanVien)
	GO



	



